<?php

class StatController extends Zend_Controller_Action {

    public function init() {
        /* Initialize action controller here */
    }

    public function indexAction() {
        
    }

    public function visiteurAction() {
        $db = Zend_Db_Table::getDefaultAdapter();
        $query = "select idVisiteur,mois,count(*) as nbFiches,sum(montantValide) as Total
            from fichefrais
            where idEtat='RB'
            group by idVisiteur,mois
            order by idVisiteur";
        $query1 = "select idVisiteur,count(*) as nbFiches,sum(montantValide) as Total
            from fichefrais
            where idEtat='RB'
            group by idVisiteur
            order by idVisiteur";
        $lesStat = $db->fetchAll($query);
        $leCumul = $db->fetchAll($query1);
        $this->view->lesStats = $lesStat;
        $this->view->leCumul = $leCumul;
    }
    
    public function fraisAction() {
        $db = Zend_Db_Table::getDefaultAdapter();

        $query = "select idVisiteur,mois,
            sum(case idFraisForfait when 'ETP' then quantite*montant end) as ETP,
            sum(case idFraisForfait when 'KM' then quantite*montant end) as KM,
            sum(case idFraisForfait when 'NUI' then quantite*montant end) as NUI,
            sum(case idFraisForfait when 'REP' then quantite*montant end) as REP
            from FraisForfait,LigneFraisForfait
            where FraisForfait.id=LigneFraisForfait.idFraisForfait
            group by idVisiteur,mois
            order by idVisiteur,mois";
        $query1 = "select idVisiteur,
            sum(case idFraisForfait when 'ETP' then quantite*montant end) as ETP,
            sum(case idFraisForfait when 'KM' then quantite*montant end) as KM,
            sum(case idFraisForfait when 'NUI' then quantite*montant end) as NUI,
            sum(case idFraisForfait when 'REP' then quantite*montant end) as REP
            from FraisForfait,LigneFraisForfait
            where FraisForfait.id=LigneFraisForfait.idFraisForfait
            group by idVisiteur
            order by idVisiteur";
        $lesStat = $db->fetchAll($query);
        $this->view->laStat = $lesStat;
        $lecumul = $db->fetchAll($query1);
        $this->view->leCumul = $lecumul;
    }

}

